package com.travel.core;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.json.JSONArray;
import org.json.JSONObject;

import com.mysql.cj.jdbc.exceptions.CommunicationsException;

import BUCF.spot_time;
import HotelRecommend.hotel;
import RestrantRecommend.shop;

/**
 * 数据库工具集
 *
 * @author SAGITTAR
 */
public abstract class AbstractDao extends BaseDataSource {

    private static final Logger LOGGER = LogManager.getLogger(AbstractDao.class);

    /**
     * 查询指定规则的统计数据
     *
     * @param sql
     * @param params
     * @return
     */
    protected static Double selectNumberByClassTypeAndSql(String sql, Object... params) {
        LOGGER.debug(getLogInfo(Objects.nonNull(params) && params.length > 0), sql, Arrays.asList(params));
        double result = -1;
        try {
            Number number = new QueryRunner(getMysqlDataSource()).query(sql, new ScalarHandler<>(), params);
            result = number.doubleValue();
        } catch (SQLException e) {
            printErrorLog(e);
        }
        LOGGER.debug(getResultLog(), result);
        return result;
    }

    /**
     * 查询指定类型对象
     *
     * @param cls 指定的类型
     * @param sql SQL语句
     * @param params SQL参数
     * @return
     */
    protected static <T> T selectOneByClassTypeAndSql(Class<T> cls, String sql, Object... params) {
        LOGGER.debug(getLogInfo(Objects.nonNull(params) && params.length > 0), sql, Arrays.asList(params));
        T entity = null;
        try {
            entity = new QueryRunner(getMysqlDataSource()).query(sql, new BeanHandler<>(cls), params);
        } catch (SQLException e) {
            printErrorLog(e);
        }
        LOGGER.debug(getResultLog(), new JSONObject(entity));
        return entity;
    }

    /**
     * 查询指定类型的列表
     *
     * @param cls 指定的类型
     * @param sql SQL语句
     * @param params SQL参数
     * @return
     */
    protected static <T> List<T> selectListByClassTypeAndSql(Class<T> cls, String sql, Object... params) {
        LOGGER.debug(getLogInfo(Objects.nonNull(params) && params.length > 0), sql, Arrays.asList(params));
        List<T> resultList = null;
        try {
            resultList = new QueryRunner(getMysqlDataSource()).query(sql, new BeanListHandler<>(cls), params);
        } catch (SQLException e) {
            printErrorLog(e);
        }
        LOGGER.debug(getResultLog(), new JSONArray(resultList));
        return resultList;
    }

    /**
     * 查询组装对象
     *
     * @param sql SQL语句
     * @param params 参数列表
     * @return
     */
    protected static Map<String, Object> selectMapBySqlAndParam(String sql, Object... params) {
        LOGGER.debug(getLogInfo(Objects.nonNull(params) && params.length > 0), sql, Arrays.asList(params));
        Map<String, Object> resultMap = null;
        try {
            resultMap = new QueryRunner(getMysqlDataSource()).query(sql, new MapHandler(), params);
        } catch (SQLException e) {
            printErrorLog(e);
        }
        LOGGER.debug(getResultLog(), new JSONObject(resultMap));
        return resultMap;
    }

    /**
     * 查询组装对象的列表
     *
     * @param sql SQL语句
     * @param params 参数列表
     * @return
     */
    protected static List<Map<String, Object>> selectMapListBySqlAndParam(String sql, Object... params) {
        LOGGER.debug(getLogInfo(Objects.nonNull(params) && params.length > 0), sql, Arrays.asList(params));
        List<Map<String, Object>> resultMapList = null;
        try {
            resultMapList = new QueryRunner(getMysqlDataSource()).query(sql, new MapListHandler(), params);
        } catch (SQLException e) {
            printErrorLog(e);
        }
        LOGGER.debug(getResultLog(), new JSONArray(resultMapList));
        return resultMapList;
    }

    /**
     * 执行插入操作，返回插入的对象
     *
     * @param cls 插入类型
     * @param sql 插入语句
     * @param params 参数列表
     * @return 插入的对象
     */
    protected static <T> T insert(Class<T> cls, String sql, Object... params) {
        T entity = null;
        try {
            entity = new QueryRunner(getMysqlDataSource()).insert(sql, new BeanHandler<>(cls), params);
        } catch (SQLException e) {
            printErrorLog(e);
        }
        return entity;
    }

    /**
     * 修改操作，返回影响行数
     *
     * @param sql 更新语句
     * @param params 参数列表
     * @return 影响行数
     */
    protected static int update(String sql, Object... params) {
        int result = -1;
        try {
            result = new QueryRunner(getMysqlDataSource()).update(sql, params);
        } catch (SQLException e) {
            printErrorLog(e);
        }
        return result;
    }

    /**
     * 拼接查询语句的日志信息
     * <p>
     * 如果有参数，返回格式为“查询语句为SELECT ……，参数列表为……”；否则返回格式为“查询语句为SELECT ……”
     *
     * @param params 是否有参数列表
     * @return
     */
    protected static String getLogInfo(boolean hasParams) {
        StringBuilder logBuilder = new StringBuilder("查询语句为{}");
        if (hasParams) {
            logBuilder.append("，参数列表为{}");
        }
        return logBuilder.toString();
    }

    /**
     * 查询结果日志格式
     *
     * @return
     */
    protected static String getResultLog() {
        return "查询结果为{}";
    }

    /**
     * 排序方向
     *
     * @author SAGITTAR
     */
    public static enum Direction {
        /**
         * 升序
         */
        ASC,
        /**
         * 降序
         */
        DESC;
    }

    /**
     * 捕获异常
     *
     * @param e
     */
    protected static void printErrorLog(SQLException e) {
        LOGGER.error(e.getLocalizedMessage(), e);
        if (e instanceof CommunicationsException) {
            LOGGER.error("数据库访问异常，请检查数据库配置并重新生成或重启服务");
        }
    }

    // 以下代码有望迁移到相应的操作实现类中

    // 获取游客玩过的景点及时间
    public static Set<spot_time> getSpotTime(String user) {
        Set<spot_time> userStep = new HashSet<>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = AbstractDao.getMysqlConnection();
            String sql = "select distinct spotName,date from tour where userId=?";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, user);
            // pstmt.setString(2, spot);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String spotName = rs.getString("spotName");
                java.sql.Timestamp time = rs.getTimestamp("date");
                userStep.add(new spot_time(spotName, time));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pstmt != null)
                    pstmt.close();
                if (con != null)
                    con.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return userStep;
    }

    // 获取景点名称
    public static String getSpotName(String spotId) {
        String spotName = null;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // String tablename="user_"+user;
        try {
            con = AbstractDao.getMysqlConnection();
            String sql = "select spotName from tour where spotId=?";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, spotId);
            // pstmt.setString(2, spot);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                spotName = rs.getString("spotName");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭操作保持一个一个关闭，如果其中一个未关闭则后面所有资源都无法关闭。如果使用QueryRunner可不用该方式关闭
            if (Objects.nonNull(rs)) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    printErrorLog(e);
                }
            }
            if (Objects.nonNull(pstmt)) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    printErrorLog(e);
                }
            }
            if (Objects.nonNull(con)) {
                try {
                    con.close();
                } catch (SQLException e) {
                    printErrorLog(e);
                }
            }
        }
        return spotName;
    }

    // 获取用户名称
    public static String getUserName(String user) {
        String userName = null;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // String tablename="user_"+user;
        try {
            con = AbstractDao.getMysqlConnection();
            String sql = "select userName from tour where userId=?";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, user);
            // pstmt.setString(2, spot);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                userName = rs.getString("userName");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭操作保持一个一个关闭，如果其中一个未关闭则后面所有资源都无法关闭。如果使用QueryRunner可不用该方式关闭
            try {
                if (rs != null)
                    rs.close();
                if (pstmt != null)
                    pstmt.close();
                if (con != null)
                    con.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return userName;
    }

    // 获取某用户对某景点的评分
    public static Integer getRating(String user, String spot) {
        int rating = 0;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // String tablename="user_"+user;
        try {
            con = AbstractDao.getMysqlConnection();
            String sql = "select rating from tour where userId=? and spotId=?";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, user);
            pstmt.setString(2, spot);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                rating = rs.getInt("rating");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pstmt != null)
                    pstmt.close();
                if (con != null)
                    con.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return rating;
    }

    // 获取与用户去过相同景点的用户集合
    public static Set<String> getValuableUsers(String user) {
        Set<String> users = new HashSet<>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // String tablename="user_"+user;
        try {
            con = AbstractDao.getMysqlConnection();
            String sql = "select b.userId as userId from tour a inner join tour b on a.userId=? and a.spotId=b.spotId";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, user);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                users.add(rs.getString("userId"));
                // System.out.println(rs.getString("object_id"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pstmt != null)
                    pstmt.close();
                if (con != null)
                    con.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return users;
    }

    // 实现一个用户评分项目集合
    public static Set<String> getRatedItems(String user) {
        Set<String> ratedItems = new HashSet<>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // String tablename="user_"+user;
        try {
            con = AbstractDao.getMysqlConnection();
            String sql = "select spotId from tour where userId=?";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, user);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                ratedItems.add(rs.getString("spotId"));
                // System.out.println(rs.getString("object_id"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pstmt != null)
                    pstmt.close();
                if (con != null)
                    con.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return ratedItems;
    }

    // 实现两个用户评分项目集合交集
    public static Set<String> getCommonRatedItems(String user1, String user2) {
        Set<String> ratedItems = new HashSet<>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // String tablename="user_"+user;
        try {
            con = AbstractDao.getMysqlConnection();
            String sql = "SELECT DISTINCT a.spotId FROM tour a INNER JOIN tour b ON a.spotId=b.spotId AND a.userId=? AND b.userId=?";
            // String sql="SELECT DISTINCT spotId FROM tour WHERE userId=? AND
            // spotId IN(SELECT spotId FROM tour WHERE
            // userId=?)";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, user1);
            pstmt.setString(2, user2);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                ratedItems.add(rs.getString("spotId"));
                // System.out.println(rs.getString("object_id"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pstmt != null)
                    pstmt.close();
                if (con != null)
                    con.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return ratedItems;
    }

    // 实现两个用户评分项目集合（并集）
    public static Set<String> getAllRatedItems(String user1, String user2) {
        Set<String> ratedItems = new HashSet<>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // String tablename="user_"+user;
        try {
            con = AbstractDao.getMysqlConnection();
            String sql = "select distinct spotId from tour where userId in(?,?)";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, user1);
            pstmt.setString(2, user2);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                ratedItems.add(rs.getString("spotId"));
                // System.out.println(rs.getString("object_id"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pstmt != null)
                    pstmt.close();
                if (con != null)
                    con.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return ratedItems;
    }

    // 获取餐饮推荐列表
    public static List<shop> getShops(String sql) {
        List<shop> shops = new ArrayList<>();
        Connection con = null;
        java.sql.Statement stmt = null;
        // PreparedStatement pstmt=null;
        ResultSet rs = null;
        // String tablename="user_"+user;
        try {
            con = AbstractDao.getMysqlConnection();
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                shop s = new shop();
                s.setShopid(rs.getString("shopid"));
                s.setShopname(rs.getString("shopname"));
                s.setLevel(rs.getString("level"));
                s.setComment(rs.getDouble("comment"));
                s.setPrice(rs.getDouble("price"));
                s.setTaste(rs.getDouble("taste"));
                s.setEnvironment(rs.getDouble("environment"));
                s.setService(rs.getDouble("service"));
                s.setCategory(rs.getString("category"));
                s.setDistrict(rs.getString("district"));
                s.setAdress(rs.getString("adress"));
                s.setShopurl(rs.getString("shopurl"));
                s.setImgurl(rs.getString("imgurl"));
                shops.add(s);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
                if (con != null)
                    con.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return shops;
    }

    // 获取酒店推荐列表
    public static List<hotel> getHotel(String sql) {
        List<hotel> hotels = new ArrayList<>();
        Connection con = null;
        java.sql.Statement stmt = null;
        // PreparedStatement pstmt=null;
        ResultSet rs = null;
        // String tablename="user_"+user;
        try {
            con = AbstractDao.getMysqlConnection();
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                hotel h = new hotel();
                h.setHotelid(rs.getString("hotelid"));
                h.setHotelname(rs.getString("hotelname"));
                h.setLevel(rs.getString("level"));
                h.setPrice(rs.getDouble("price"));
                h.setRating(rs.getDouble("rating"));
                h.setRecommend(rs.getDouble("recommend"));
                h.setComment(rs.getDouble("comment"));
                h.setBusinessarea(rs.getString("businessarea"));
                h.setAdress(rs.getString("adress"));
                h.setLabel(rs.getString("label"));
                h.setHotelurl(rs.getString("hotelurl"));
                h.setImgurl(rs.getString("imgurl"));
                h.setDistrict(rs.getString("district"));
                hotels.add(h);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
                if (con != null)
                    con.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return hotels;
    }

    /**
     * 获取MySQL数据源对应的连接
     *
     * @return
     */
    // 待以上代码迁移之后可删除该方法
    private static Connection getMysqlConnection() {
        LOGGER.debug("申请MySQL数据库连接");
        Connection connection = null;
        try {
            connection = getMysqlDataSource().getConnection();
        } catch (SQLException e) {
            printErrorLog(e);
        }
        return connection;
    }
}
